﻿using System;
using System.Collections.Generic;
using System.Text;
using Business.Entities;
using System.Data;
using System.Data.SqlClient;

namespace Data.Database
{
    public class PlanesAdapter:Adapter
    {
        private static List<Plan> _Plann;

        private static List<Plan> Plann
        {
            get
            {
                if (_Plann == null)
                {
                    _Plann = new List<Plan>();
                }
                return _Plann;
            }
        }

        public List<Plan> GetAll() //correcto
        {
            try
            {
                this.OpenConnection();
                SqlCommand cmdSelectPlan = new SqlCommand("select * from planes p inner join " +
                    "especialidades e on p.id_especialidad=e.id_especialidad " +
                    "order by e.desc_especialidad, p.desc_plan", sqlConn);
                SqlDataReader reader = cmdSelectPlan.ExecuteReader();
                List<Plan> ListaPlanes = new List<Plan>();
                while (reader.Read())
                {
                    Plan PlanActual = new Plan();
                    PlanActual.ID = (int)reader["id_plan"];
                    PlanActual.Descripcion = (string)reader["desc_plan"];
                    PlanActual.IDEspecialidad= (int)reader["id_especialidad"];
                    PlanActual.DescripcionEspecialidad=(string)reader["desc_especialidad"];
                    ListaPlanes.Add(PlanActual);
                }
                reader.Close();
                return ListaPlanes;
            }
            catch (Exception ex)
            {
                Exception exManejada = new Exception("Error al actualizar en base de datos", ex);
                throw exManejada;
            }
            finally
            {
                this.CloseConnection();
            }

        }

        public Plan GetOne(int ID) //correcto
        {
            try
            {
                this.OpenConnection();
                SqlCommand cmdGetPlan = new SqlCommand("select * from planes p where @id=p.id_plan", sqlConn);
                cmdGetPlan.Parameters.Add("@id", typeof(int)).Value = ID;
                SqlDataReader sqlreader = cmdGetPlan.ExecuteReader();
                Plan PlanEncontrado = new Plan();
                while (sqlreader.Read())
                {
                    PlanEncontrado.ID = (int)sqlreader["id_plan"];
                    PlanEncontrado.Descripcion = (string)sqlreader["desc_plan"];
                    PlanEncontrado.IDEspecialidad = (int)sqlreader["id_especialidad"];
                }
                sqlreader.Close();
                return PlanEncontrado;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }

        public void Delete(Plan pl) //correcto
        {
            try
            {
                Plann.Remove(pl);
                this.OpenConnection();
                SqlCommand cmdPlan = new SqlCommand("delete from planes where id_plan=@id_pl", sqlConn);
                cmdPlan.Parameters.Add("@id_pl", typeof(int)).Value = pl.ID;
                cmdPlan.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }

        public void Save(Plan plan) //correcto
        {
            try
            {
                this.OpenConnection();
                if (plan.State == Plan.States.New)
                {
                    SqlCommand cmdPlan = new SqlCommand("insert into planes(desc_plan, id_especialidad) " +
                            "values(@desc_plan, @id_especialidad)", this.sqlConn);
                    cmdPlan.Parameters.Add("@desc_plan", typeof(string)).Value = plan.Descripcion;
                    cmdPlan.Parameters.Add("@id_especialidad", typeof(int)).Value = plan.IDEspecialidad;
                    cmdPlan.ExecuteNonQuery();
                }

                else if (plan.State == Plan.States.Modified)
                {
                    SqlCommand cmdPlan = new SqlCommand("update planes set desc_plan=@desc_plan, " +
                     "id_especialidad=@id_especialidad where id_plan=@id_plan", this.sqlConn);
                    cmdPlan.Parameters.Add("@id_plan", typeof(int)).Value = plan.ID;
                    cmdPlan.Parameters.Add("@desc_plan", typeof(string)).Value = plan.Descripcion;
                    cmdPlan.Parameters.Add("@id_especialidad", typeof(int)).Value = plan.IDEspecialidad;
                    cmdPlan.ExecuteNonQuery();
                }
                else if (plan.State == Materias.States.Unmodified)
                {

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }     

    }
}
